package cn.edu.cug.cs.gtl.utils.impl;

import cn.edu.cug.cs.gtl.protos.DataItem;
import cn.edu.cug.cs.gtl.protos.DataKey;
import cn.edu.cug.cs.gtl.utils.DataItemManager;
import cn.edu.cug.cs.gtl.utils.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DataItemManagerImpl implements DataItemManager {
    DatabaseUtil databaseUtil = new DatabaseUtil();

    @Override
    public void insertDataItem(DataItem dataItem) {
        Connection con = databaseUtil.getConnection();

        String insertSQL = "insert into data_item(name,alias,description,format,data_key) values(?,?,?,?,?)";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(insertSQL);
            preparedStatement.setString(1, dataItem.getName());
            preparedStatement.setString(2, dataItem.getAlias());
            preparedStatement.setString(3, dataItem.getDescription());
            preparedStatement.setString(4, dataItem.getFormat());
            //preparedStatement.setString(5,dataItem.getKey().toString());
            DataKey dataKey = dataItem.getKey();
            byte[] key = dataKey.toByteArray();
            //byte[] key = DatabaseUtil.serializeToString(dataKey);
            preparedStatement.setBytes(5, key);

            int rowsAffected = preparedStatement.executeUpdate();

            //检查是否成功插入一条记录
            if (rowsAffected > 0) {
                System.out.println(dataItem.getName() + "插入成功");
            } else {
                System.err.println("插入失败");
            }

            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
    }

    @Override
    public DataItem removeDataItem(String dataItemName) {
        //首先检查是否存在指定的数据项
        DataItem existingDataItem = getDataItem(dataItemName);

        if (existingDataItem != null) {
            Connection con = databaseUtil.getConnection();

            String deleteSQL = "DELETE from data_item where name=?";

            try {
                PreparedStatement preparedStatement = con.prepareStatement(deleteSQL);

                preparedStatement.setString(1, dataItemName);

                int rowsAffected = preparedStatement.executeUpdate();

                // 检查是否成功删除了一行记录，成功删除返回dataItem
                if (rowsAffected > 0) {
                    System.out.println(dataItemName + "删除成功");
                    return existingDataItem;
                } else {
                    System.err.println(dataItemName + "删除失败");
                }

                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                databaseUtil.close(con);
            }
        } else {
            System.err.println(dataItemName + "不存在");
        }
        return null;
    }

    @Override
    public void updateDataItem(DataItem dataItem) {
        Connection con = databaseUtil.getConnection();

        String updateSQL = "update data_item set alias=?,description=?,format=?,data_key=? where name=?";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(updateSQL);

            preparedStatement.setString(1, dataItem.getAlias());
            preparedStatement.setString(2, dataItem.getDescription());
            preparedStatement.setString(3, dataItem.getFormat());
            //preparedStatement.setString(4,dataItem.getKey().toString());
            DataKey dataKey = dataItem.getKey();
            byte[] key = dataKey.toByteArray();
            //byte[] key = DatabaseUtil.serializeToString(dataKey);
            preparedStatement.setBytes(4, key);

            preparedStatement.setString(5, dataItem.getName());

            int rowsAffected = preparedStatement.executeUpdate();

            // 检查是否成功更新了一行记录
            if (rowsAffected > 0) {
                System.out.println(dataItem.getName() + "更新成功");
            } else {
                System.err.println(dataItem.getName() + "更新失败");
            }

            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
    }

    @Override
    public void updateDataItem(DataItem dataItem, int orderId, int gid) {
        Connection con = databaseUtil.getConnection();

        String updateSQL = "update data_item set alias=?,description=?,order_id=?,format=?,gid=?,data_key=? where name=?";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(updateSQL);

            preparedStatement.setString(1, dataItem.getAlias());
            preparedStatement.setString(2, dataItem.getDescription());
            preparedStatement.setInt(3, orderId);
            preparedStatement.setString(4, dataItem.getFormat());
            preparedStatement.setInt(5, gid);
            //preparedStatement.setString(4,dataItem.getKey().toString());
            DataKey dataKey = dataItem.getKey();
            byte[] key = dataKey.toByteArray();
            //byte[] key = DatabaseUtil.serializeToString(dataKey);
            preparedStatement.setBytes(6, key);

            preparedStatement.setString(7, dataItem.getName());

            int rowsAffected = preparedStatement.executeUpdate();

            // 检查是否成功更新了一行记录
            if (rowsAffected > 0) {
                System.out.println(dataItem.getName() + "更新成功");
            } else {
                System.err.println(dataItem.getName() + "更新失败");
            }

            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
    }


    @Override
    public DataItem getDataItem(String dataItemName) {
        Connection con = databaseUtil.getConnection();

        String getSQL = "select * from data_item where name=?";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(getSQL);
            preparedStatement.setString(1, dataItemName);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                String name = resultSet.getString("name");
                String alias = resultSet.getString("alias");
                String description = resultSet.getString("description");
                String format = resultSet.getString("format");

                //反序列化得到dataKey
                //byte[] key=resultSet.getBytes("data_key");
                //DataKey dataKey = DatabaseUtil.deserializeFromString(key);

                DataKey dataKey = DataKey.parseFrom(resultSet.getBytes("data_key"));

                // 创建 DataItem 对象并设置属性值，包括设置 DataKey
                DataItem dataItem = DataItem.newBuilder()
                        .setName(name)
                        .setAlias(alias)
                        .setDescription(description)
                        .setFormat(format)
                        .setKey(dataKey)
                        .build();

                resultSet.close();
                preparedStatement.close();

                return dataItem;
            } else {
                System.err.println(dataItemName + "不存在");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
        return null;
    }

    @Override
    public int getIdByName(String dataItemName) {
        Connection con = databaseUtil.getConnection();

        String selectSQL = "select id from data_item where name = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(selectSQL);
            preparedStatement.setString(1, dataItemName);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                return resultSet.getInt("id");
            } else {
                System.err.println(dataItemName + "不存在");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
        return -1;
    }

    @Override
    public String getNameById(int id) {
        Connection con = databaseUtil.getConnection();

        String selectSQL = "select name from data_item where id = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(selectSQL);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()) {
                return resultSet.getString("name");
            } else {
                System.err.println(id + "不存在");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
        return null;
    }

    @Override
    public List<DataItem> getList(String itemName) {
        Connection con = databaseUtil.getConnection();

        List<DataItem> list = new ArrayList<>();
        String selectSQL = "select * from data_item where alias like ? and gid is null ";
        try {
            PreparedStatement statement = con.prepareStatement(selectSQL);
            statement.setString(1, "%" + itemName + "%");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(2);
                String alias = resultSet.getString(3);
                String description = resultSet.getString(4);
                String format = resultSet.getString(6);

                DataItem dataItem = DataItem.newBuilder()
                        .setName(name)
                        .setAlias(alias)
                        .setDescription(description)
                        .setFormat(format)
                        .build();

                list.add(dataItem);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            databaseUtil.close(con);
        }
        return list;
    }
}
